Quick Formulas
Quick Formulas allow users to quickly build mathematical and statistical logic on the selected items a single step. The formula options exposed in the 'quick' menus is directed a the typical listing of functions users often need and are driven by the type of members selected (dates, measures, normal hierarchy elements etc) and the number of elements selected (one, two or more). All quick formulas can be fully customized and edited using the Formulate tools - which allow the user to construct ANY type of mathematical operation possible within the given data model and language (PQL for SQL data sources and MDX for others).
Note: All quick formulas built directly in Discover are initially built as one-of, report specific items. However, they can be promoted a sharable item. See here for more.
Types of Quick Formulas
The heuristic engine in Pyramid changes the types of quick formulas available based on what is selected. The selections can be made from both the element trees or directly from visuals.
Single Element
Math functions
These methods are classic mathematical operations for any element on both hierarchies and measures. For example Absolute value.
Date time functions
These methods are designed to produce date time logic on elements from date-time hierarchies only. They will only work on non-year elements. For example Year-to-date.
Note: The type of date functions exposed will change based on the hierarchy type. So The options for quarters is different to those for months or actual dates.
Note: Two extra functions are available for MDX data sources (like MS OLAP, Tabular and SAP BW): Parallel Period and Periods to Date
Hierarchical Function
These methods are designed to produce hierarchical logic on element from regular, multilevel hierarchies only. For example Percentage of Parent.
Dual Elements
Constructed Functions
These methods offer basic math operations using the 2 selected elements or measures. This includes addition, percentage variance and ratio.
Note: The custom measure calculation will be applied according to the order in which the items are selected. For example, to subtract Returns is subtracted from Quantity, be sure to select Quantity first, and then Returns.
Range Aggregate Functions
These methods offer statistical aggregation logic using the RANGE of elements BETWEEN the 2 selected items. This means that picking the "average" range aggregation using January and June, will build a formula of the average across all the months between January and June inclusive.
Aggregate Functions
These methods offer statistical aggregation logic using the selected elements only. This means that picking the "average" aggregation using January and June, will build a formula of the average across January and June only.
Notes: There are no special date-time formulas when using 2 or more date time elements.
Note: There are no aggregation function options for measures apart from "SUM"
Multiple Elements
Once more than 2 elements have been chosen, the only quick calc options available are "aggregate functions" - described above. Further, the only measure based function is a simple "sum" operation.
Building Quick Formulas
To build quick calculations start by making selections of the relevant elements. Then right click, and choose the relevant calculation from the context menus. See examples and the below and the attached video for more.
- Click here to see a demo of how to build formulas.
Managing Quick Formulas
Formulas created from the quick menus can be fully edited, deleted and shared like any other formula item in the system. Click here to read more on how to manage quick formulas.
Examples
To build quick formula from the Elements panels, Ctrl + click on the elements (without selecting their checkboxes ) that you want you calculation to include.
Right click on one of the selected elements, and choose the required calculation: custom member formula.
From a grid visual (or any visual), select the relevant member elements (as described here). Right click on the selection and from the Quick Calc sub-menu, create a formula.
Quick custom measure calculations can be configured in a few ways:
Ctrl + click and select the required measures from the Measures tree panel. Right click and select custom measure (sum). The calculation options will be larger if its only a single measure.
From a grid visualizationuse the selection tools to select the required measures, then right click and choose a calculation from the context menu.
From a chart visualization, right click on the relevant measure label. From the context menu, create measure.